from src.db import db_models
from tortoise.functions import *


async def over30():
    ret = await db_models.Stu_Info.filter(stu_age__gt=30).all()
    return ret


async def boysandgirls():
    stus = await (db_models.Stu_Info.
                  # filter(stu_gender='男').
                  annotate(count=Count('stu_id')).
                  group_by('stu_class_name', 'stu_gender').
                  values('stu_class_name', 'stu_gender', 'count'))

    for i in stus:
        i['班级'] = i.pop('stu_class_name')
        i['性别'] = i.pop('stu_gender')
        i['人数'] = i.pop('count')
    return {"各班级男女人数": stus}


async def get_high_score():
    ret = await (
        db_models.Score.
        annotate(min_score=Min('stu_grade')).
        group_by('stu_id_id').
        values('stu_id_id', 'min_score')
    )
    re = []
    for i in ret:
        if i['min_score'] > 80:
            stu = await db_models.Stu_Info.filter(stu_id=i['stu_id_id']).prefetch_related('score').first()
            re.append({
                '学生': stu.stu_id,
                '姓名': stu.stu_name,
                '成绩': [{'考核序次': i.test_time, '分数': i.stu_grade} for i in stu.score]
            })

    return re


async def bad_students():
    bad_stu = await (db_models.Score.
                     filter(stu_grade__lt=60).
                     annotate(bad_time=Count('id')).
                     group_by('stu_id_id').
                     # filter(bad_time__gte=2).
                     values('stu_id_id', 'bad_time'))
    re = []
    for i in bad_stu:
        if i['bad_time'] >= 2:
            stu = await db_models.Stu_Info.filter(stu_id=i['stu_id_id']).prefetch_related('score').first()
            re.append({
                '学生': stu.stu_id,
                '姓名': stu.stu_name,
                '成绩': [{'考核序次': i.test_time, '分数': i.stu_grade} for i in stu.score]
            })
    return re


async def top5_job():
    top_job = await (db_models.Job.
                      all().
                      order_by('-salary').
                      prefetch_related('stu_id').
                      all()
                      )
    cnt = 0
    stu = []
    re = []
    for i in top_job:
        if i.salary not in stu:
            stu.append(i.salary)
            re.append({
                '姓名': i.stu_id.stu_name,
                '班级': i.stu_id.stu_class_name,
                '就业时间': i.offer_time,
                '公司': i.company_name
            })
            cnt += 1
        else:
            stu.append(i.stu_id)
            re.append({
                '姓名': i.stu_id.stu_name,
                '班级': i.stu_id.stu_class_name,
                '就业时间': i.offer_time,
                '公司': i.company_name
            })
        if cnt >= 5:
            break
    return re
